﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Threading;
using Common.Utilities;
using Common.Utilities.Dispatcher;
using Common.Utilities.OpenXml;

namespace Workflows.Components.ETL
{
    /// <summary>
    /// 
    /// </summary>
    public class DownloadTablesWorker:IWorker 
    {
        #region Implementation of IWorker

        public event WorkerStarted WorkerStartedEvent;
        public event WorkerFinished WorkerFinishedEvent;
        public event WorkerHasError WorkerErredEvent;
        public event WorkerCancelled WorkerCancelledEvent;
        public event WorkerStatusChanged WorkerStatusChangedEvent;

        private Thread workerThread;
        private DownloadTablesInstruction instruction;
        private DownloadTablesOutput output;
        private RunContext _Context;
        private Guid _TicketID;
        private string _WorkflowName;
        private string _ComponentName;

        /// <summary>
        /// 
        /// </summary>
        public string ComponentName
        {
            get { return _ComponentName; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string WorkflowName
        {
            get { return _WorkflowName; }
        }

        /// <summary></summary>
        public Thread WorkerThread
        {
            get { return workerThread; }
        }
        /// <summary></summary>
        public InstructionBase Instruction
        {
            get { return instruction; }
        }
        /// <summary></summary>
        public ResultBase Output
        {
            get { return output; }
        }
        /// <summary></summary>
        public Type ComponentType
        {
            get { return typeof(DownloadTablesComponent); }
        }
        /// <summary></summary>
        public RunContext Context
        {
            get { return _Context; }
            set { _Context = value; }
        }
        /// <summary></summary>
        public void SetInstruction(Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._TicketID = ticketGuid;
            this.instruction = (DownloadTablesInstruction)request;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }
        /// <summary></summary>
        public void SetInstruction(RunContext context, Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._TicketID = ticketGuid;
            this.instruction = (DownloadTablesInstruction)request;
            this._Context = context;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }
        /// <summary></summary>
        public void DoWork()
        {
            if (this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this.instruction);
            }
            this.workerThread = Thread.CurrentThread;
            this.Download();
        }
        /// <summary></summary>
        public void DoWorkAsync()
        {
            if (this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this.instruction);
            }
            this.workerThread = new Thread(new ThreadStart(this.Download));
            this.workerThread.Start();
        }
        /// <summary></summary>
        public void CancelWork()
        {
            if (this.workerThread != null && this.workerThread.IsAlive)
            {
                this.workerThread.Abort();
                this.workerThread.Join();

                if (this.WorkerCancelledEvent != null)
                {
                    this.WorkerCancelledEvent(this._TicketID, this.GetType(), this.instruction);
                }
            }
        }

        #endregion

        #region work
        private void Download()
        {
            SqlConnection conn=new SqlConnection(this.instruction.ConnStr);
            int tblCount = 0;
            int totalTables = 0;
            if (this.instruction.TablesBySchema != null && this.instruction.TablesBySchema.Count > 0)
            {
                foreach (string schema in this.instruction.TablesBySchema.Keys)
                {
                    totalTables += this.instruction.TablesBySchema[schema].Count;
                }
            }
            if (this.instruction.TablesBySql != null && this.instruction.TablesBySql.Count > 0)
            {
                totalTables += this.instruction.TablesBySql.Count;
            }
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                if (this.instruction.TgtFileFormat == FileFormat.DelimitedTextFile)
                {
                    if (this.instruction.TablesBySchema != null && this.instruction.TablesBySchema.Count > 0)
                    {
                        foreach (string schema in this.instruction.TablesBySchema.Keys)
                        {
                            foreach (string tbl in this.instruction.TablesBySchema[schema])
                            {
                                TableColumn[] cols = DbUtil.GetTableColumns(DataProviderType.MSSQL,
                                                                            this.instruction.ConnStr, schema, tbl);
                                string outputFilePath = Path.Combine(this.instruction.TargetFolderPath, tbl + ".txt");
                                if (File.Exists(outputFilePath))
                                    File.Delete(outputFilePath);
                                StreamWriter writer = new StreamWriter(outputFilePath);
                                cmd.CommandText = string.Format("select * from {0}.[{1}]", schema, tbl);
                                SqlDataReader reader = cmd.ExecuteReader();
                                this.WriteTableToTextFile(writer, reader, cols);
                                writer.Flush();
                                writer.Close();

                                tblCount++;
                                if (this.WorkerStatusChangedEvent != null)
                                {
                                    string msg = string.Format("Downloaded {0} of {1} tables...", tblCount, totalTables);
                                    int pct = tblCount * 100 / totalTables;
                                    this.WorkerStatusChangedEvent(this._TicketID, this.GetType(),
                                                                  this.instruction, this._WorkflowName, this.ComponentName,
                                                                  msg, pct);
                                }
                            }
                        }
                    }
                    if (this.instruction.TablesBySql != null && this.instruction.TablesBySql.Count > 0)
                    {
                        foreach (string tblName in this.instruction.TablesBySql.Keys)
                        {
                            string sql = this.instruction.TablesBySql[tblName];
                            TableColumn[] cols = DbUtil.GetTableColumns(DataProviderType.MSSQL,
                                                                            this.instruction.ConnStr, sql);
                            string outputFilePath = Path.Combine(this.instruction.TargetFolderPath, tblName + ".txt");
                            if (File.Exists(outputFilePath))
                                File.Delete(outputFilePath);
                            StreamWriter writer = new StreamWriter(outputFilePath);
                            cmd.CommandText = sql;
                            SqlDataReader reader = cmd.ExecuteReader();
                            this.WriteTableToTextFile(writer, reader, cols);
                            writer.Flush();
                            writer.Close();

                            tblCount++;
                            if (this.WorkerStatusChangedEvent != null)
                            {
                                string msg = string.Format("Downloaded {0} of {1} tables...", tblCount, totalTables);
                                int pct = tblCount * 100 / totalTables;
                                this.WorkerStatusChangedEvent(this._TicketID, this.GetType(),
                                                              this.instruction, this._WorkflowName, this.ComponentName,
                                                              msg, pct);
                            }
                        }
                    }
                }
                else
                {
                    if (this.instruction.TablesBySchema != null && this.instruction.TablesBySchema.Count > 0)
                    {
                        foreach (string schema in this.instruction.TablesBySchema.Keys)
                        {
                            foreach (string tbl in this.instruction.TablesBySchema[schema])
                            {
                                DataTable dt = DbUtil.GetTableData(
                                    this.instruction.ConnStr, schema, tbl);
                                string fileName = tbl;
                                if (instruction.TgtFileFormat == FileFormat.XmlFile)
                                    fileName = tbl + ".xml";
                                else if (instruction.TgtFileFormat == FileFormat.ExcelFile)
                                    fileName = tbl + ".xlsx";
                                string outputFilePath = Path.Combine(this.instruction.TargetFolderPath, fileName);
                                if (File.Exists(outputFilePath))
                                    File.Delete(outputFilePath);
                                DataSet ds = new DataSet(tbl);
                                ds.Tables.Add(dt.Copy());
                                if (instruction.TgtFileFormat == FileFormat.XmlFile)
                                    ds.WriteXml(outputFilePath);
                                else if (instruction.TgtFileFormat == FileFormat.ExcelFile)
                                    ExcelWriter.CreateWorkbook(outputFilePath, ds);

                                tblCount++;
                                if (this.WorkerStatusChangedEvent != null)
                                {
                                    string msg = string.Format("Downloaded {0} of {1} tables...", tblCount, totalTables);
                                    int pct = tblCount * 100 / totalTables;
                                    this.WorkerStatusChangedEvent(this._TicketID, this.GetType(),
                                                                  this.instruction, this._WorkflowName, this.ComponentName,
                                                                  msg, pct);
                                }
                            }
                        }
                    }

                    if (this.instruction.TablesBySql != null && this.instruction.TablesBySql.Count > 0)
                    {
                        foreach (string tblName in this.instruction.TablesBySql.Keys)
                        {
                            string sql = this.instruction.TablesBySql[tblName];
                            DataTable dt = DbUtil.GetTableData(
                                    this.instruction.ConnStr, sql);
                            string fileName = tblName;
                            if (instruction.TgtFileFormat == FileFormat.XmlFile)
                                fileName = tblName + ".xml";
                            else if (instruction.TgtFileFormat == FileFormat.ExcelFile)
                                fileName = tblName + ".xlsx";
                            string outputFilePath = Path.Combine(this.instruction.TargetFolderPath, fileName);
                            if (File.Exists(outputFilePath))
                                File.Delete(outputFilePath);
                            DataSet ds = new DataSet(tblName);
                            ds.Tables.Add(dt.Copy());
                            if (instruction.TgtFileFormat == FileFormat.XmlFile)
                                ds.WriteXml(outputFilePath);
                            else if (instruction.TgtFileFormat == FileFormat.ExcelFile)
                                ExcelWriter.CreateWorkbook(outputFilePath, ds);

                            tblCount++;
                            if (this.WorkerStatusChangedEvent != null)
                            {
                                string msg = string.Format("Downloaded {0} of {1} tables...", tblCount, totalTables);
                                int pct = tblCount * 100 / totalTables;
                                this.WorkerStatusChangedEvent(this._TicketID, this.GetType(),
                                                              this.instruction, this._WorkflowName, this.ComponentName,
                                                              msg, pct);
                            }
                        }
                    }
                }
                

                this.output=new DownloadTablesOutput(this.instruction.OwnerComponentID,this._TicketID,tblCount);
                if (this.WorkerFinishedEvent != null)
                    this.WorkerFinishedEvent(this._TicketID, this.GetType(), this.instruction, this.output);
            }
            catch(Exception ex)
            {
                if(this.WorkerErredEvent !=null)
                {
                    this.WorkerErredEvent(this._TicketID, this.GetType(), this.instruction, ex.ToString(), true);
                }
            }
            finally
            {
                conn.Close();
            }
        }

        private void WriteTableToTextFile(StreamWriter writer, SqlDataReader reader, TableColumn[] cols)
        {
            string colHeader = string.Empty;
            for(int i=0;i<cols.Length;i++)
            {
                colHeader += cols[i].ColumnName;
                if (i < cols.Length - 1)
                    colHeader += "\t";
            }
            writer.WriteLine(colHeader);

            while (reader.Read())
            {
                string line = string.Empty;
                for (int i = 0; i < cols.Length; i++)
                {
                    string colName = cols[i].ColumnName;
                    if (reader[colName] == null || reader[colName] == DBNull.Value)
                        line += "";
                    else
                        line += reader[colName].ToString();
                    if (i < cols.Length - 1)
                        line += "\t";
                }
                writer.WriteLine(line);
            }
            reader.Close();
        }
        #endregion
    }
}
